USE Fresher14_G1

------Check Table Exists

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='TimesheetDetail')
DROP TABLE TimesheetDetail

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='Timesheet')
DROP TABLE Timesheet

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='Contract')
DROP TABLE [Contract]

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='ContractType')
DROP TABLE ContractType

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='Client')
DROP TABLE Client

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='Consultant')
DROP TABLE Consultant

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='Contractor')
DROP TABLE Contractor

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='Account')
DROP TABLE Account

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='Role')
DROP TABLE [Role]

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='SystemEventLog')
DROP TABLE SystemEventLog

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='AccountEventLog')
DROP TABLE AccountEventLog

GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='ErrorEvent')
DROP TABLE ErrorEvent

------Create table

---Table Role

GO
CREATE TABLE [Role]
(
	RoleId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	RoleName NVARCHAR(30) NOT NULL,
	Status BIT NOT NULL
)

INSERT INTO [Role](RoleName,Status) VALUES ('Administrator', 'True')
INSERT INTO [Role](RoleName,Status) VALUES ('Contractor', 'True')
INSERT INTO [Role](RoleName,Status) VALUES ('Consultant', 'True')
INSERT INTO [Role](RoleName,Status) VALUES ('Client', 'True')

---Table Account

GO
CREATE TABLE Account
(
	AccountId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	RoleId UNIQUEIDENTIFIER REFERENCES [Role](RoleId) NOT NULL, 
	Username VARCHAR(50) NOT NULL,
	Password VARCHAR(50) NOT NULL,
	FullName NVARCHAR(50) NOT NULL,
	PhoneNumber1 VARCHAR(20) NOT NULL,
	PhoneNumber2 VARCHAR(20),
	Gender NVARCHAR(20) NOT NULL,
	IsActive BIT NOT NULL
)

---Table Client

GO
CREATE TABLE Client
(
	ClientId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	AccountId UNIQUEIDENTIFIER REFERENCES Account(AccountId) NOT NULL, 
	CompanyName NVARCHAR(50) NOT NULL,
	CompanyRegisterNumber INT NOT NULL,
	CompanyVatNumber INT NOT NULL,
	CompanyPostCode INT NOT NULL,
	CompanyAddress NVARCHAR(200) NOT NULL,
)

---Table Consultant

GO
CREATE TABLE Consultant
(
	ConsultantId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	AccountId UNIQUEIDENTIFIER REFERENCES Account(AccountId) NOT NULL, 
	Address NVARCHAR(100) NOT NULL,
	RegisterNumber INT NOT NULL,
	VatNumber INT NOT NULL
)


---Table Contractor

GO
CREATE TABLE Contractor
(
	ContractorId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	AccountId UNIQUEIDENTIFIER REFERENCES Account(AccountId) NOT NULL, 
	Branch CHAR(10) NOT NULL,
	SortCode CHAR(10) NOT NULL
)

---Table ContractType

GO
CREATE TABLE ContractType
(
	ContractTypeId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	ContractTypeName NVARCHAR(30) NOT NULL,
	IsActive BIT NOT NULL
)

INSERT INTO ContractType(ContractTypeName,IsActive) VALUES ('Fixed-term contract', 'True')
INSERT INTO ContractType(ContractTypeName,IsActive) VALUES ('Part-time contract', 'True')
INSERT INTO ContractType(ContractTypeName,IsActive) VALUES ('Term-time contract', 'True')
INSERT INTO ContractType(ContractTypeName,IsActive) VALUES ('Training contract', 'True')
INSERT INTO ContractType(ContractTypeName,IsActive) VALUES ('Interim contract', 'True')


---Table Contract

GO
CREATE TABLE [Contract]
(
	ContractId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	ContractTypeId UNIQUEIDENTIFIER REFERENCES ContractType(ContractTypeId) NOT NULL,
	ContractorId UNIQUEIDENTIFIER REFERENCES Contractor(ContractorId) NOT NULL,
	ConsultantId UNIQUEIDENTIFIER REFERENCES Consultant(ConsultantId) NOT NULL,
	ClientId UNIQUEIDENTIFIER REFERENCES Client(ClientId) NOT NULL,
	ContractNumber INT NOT NULL,
	[Group] NVARCHAR(50) NOT NULL,
	Title NVARCHAR(200) NOT NULL,
	Description NVARCHAR(MAX),
	[Value] MONEY NOT NULL,
	CreateDate DATETIME DEFAULT GETDATE() NOT NULL,
	StartDate DATETIME NOT NULL,
	EndDate DATETIME NOT NULL,
	IsActive BIT NOT NULL
)

---Table Timesheet

GO
CREATE TABLE Timesheet
(
	TimesheetId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	ContractId UNIQUEIDENTIFIER REFERENCES [Contract](ContractId) NOT NULL,
	CreateDate DATETIME DEFAULT GETDATE() NOT NULL,
	Status VARCHAR(30) NOT NULL,
	RejectReason NVARCHAR(MAX) NOT NULL
)

---Table TimesheetDetail

GO
CREATE TABLE TimesheetDetail
(
	TimesheetDetailId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	TimesheetId UNIQUEIDENTIFIER REFERENCES Timesheet(TimesheetId) NOT NULL,
	StartHour INT NOT NULL,
	EndHour INT NOT NULL,
	Description NVARCHAR(MAX)
)

---Table SystemEventLog

GO
CREATE TABLE SystemEventLog
(
	SystemEventLogId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	Date DATETIME DEFAULT GETDATE() NOT NULL,
	SystemEventType NVARCHAR(50) NOT NULL,
	Description NVARCHAR(MAX)
)

---Table AccountEventLog

GO
CREATE TABLE AccountEventLog
(
	AccountEventLogId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	Date DATETIME DEFAULT GETDATE() NOT NULL,
	ActionName NVARCHAR(50) NOT NULL,
	Description NVARCHAR(MAX)
)

---Table ErrorEvent

GO
CREATE TABLE ErrorEvent
(
	ErrorEventId UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID() PRIMARY KEY,
	Date DATETIME DEFAULT GETDATE() NOT NULL,
	ErrorMessage NVARCHAR(200) NOT NULL,
	Description NVARCHAR(MAX)
)